"""
数据库模型和操作
使用 SQLite 存储用户信息和对话历史
"""
import os
import sqlite3
from datetime import datetime
from typing import Optional, List, Dict
from passlib.context import CryptContext

# 密码加密上下文
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")

# 数据库文件路径
DB_PATH = os.path.join(os.path.dirname(os.path.dirname(__file__)), "data", "text2code.db")
os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)


def get_db():
    """获取数据库连接"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row  # 返回字典形式的行
    return conn


def init_db():
    """初始化数据库表"""
    conn = get_db()
    cursor = conn.cursor()
    
    # 用户表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            email TEXT UNIQUE NOT NULL,
            hashed_password TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    
    # 对话历史表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS conversations (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            prompt TEXT NOT NULL,
            generated_code TEXT NOT NULL,
            is_valid BOOLEAN DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )
    """)
    
    # 用户统计表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS user_stats (
            user_id INTEGER PRIMARY KEY,
            total_generations INTEGER DEFAULT 0,
            perfect_scores INTEGER DEFAULT 0,
            good_scores INTEGER DEFAULT 0,
            total_score_sum INTEGER DEFAULT 0,
            consecutive_days INTEGER DEFAULT 0,
            last_active_date DATE,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )
    """)
    
    # 成就表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS achievements (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            achievement_type TEXT NOT NULL,
            achievement_name TEXT NOT NULL,
            unlocked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )
    """)
    
    # 创建索引提升查询性能
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_user_id ON conversations(user_id)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_created_at ON conversations(created_at)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_achievements_user ON achievements(user_id)")
    
    conn.commit()
    conn.close()
    print(f"[INFO] 数据库初始化完成: {DB_PATH}")


# ===================== #
# 用户相关操作
# ===================== #

def hash_password(password: str) -> str:
    """密码哈希"""
    return pwd_context.hash(password)


def verify_password(plain_password: str, hashed_password: str) -> bool:
    """验证密码"""
    return pwd_context.verify(plain_password, hashed_password)


def create_user(username: str, email: str, password: str) -> Optional[int]:
    """创建用户,返回用户ID"""
    conn = get_db()
    cursor = conn.cursor()
    try:
        hashed_pwd = hash_password(password)
        cursor.execute(
            "INSERT INTO users (username, email, hashed_password) VALUES (?, ?, ?)",
            (username, email, hashed_pwd)
        )
        conn.commit()
        user_id = cursor.lastrowid
        return user_id
    except sqlite3.IntegrityError:
        return None  # 用户名或邮箱已存在
    finally:
        conn.close()


def get_user_by_username(username: str) -> Optional[Dict]:
    """根据用户名获取用户信息"""
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE username = ?", (username,))
    row = cursor.fetchone()
    conn.close()
    return dict(row) if row else None


def get_user_by_email(email: str) -> Optional[Dict]:
    """根据邮箱获取用户信息"""
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE email = ?", (email,))
    row = cursor.fetchone()
    conn.close()
    return dict(row) if row else None


def authenticate_user(username: str, password: str) -> Optional[Dict]:
    """验证用户登录,返回用户信息"""
    user = get_user_by_username(username)
    if not user:
        return None
    if not verify_password(password, user["hashed_password"]):
        return None
    return user


# ===================== #
# 对话历史相关操作
# ===================== #

def save_conversation(user_id: int, prompt: str, generated_code: str, is_valid: bool = True) -> int:
    """保存对话记录,返回记录ID"""
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO conversations (user_id, prompt, generated_code, is_valid) VALUES (?, ?, ?, ?)",
        (user_id, prompt, generated_code, is_valid)
    )
    conn.commit()
    conv_id = cursor.lastrowid
    conn.close()
    return conv_id


def get_user_conversations(user_id: int, limit: int = 50, offset: int = 0) -> List[Dict]:
    """获取用户的对话历史(分页)"""
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute(
        """
        SELECT id, prompt, generated_code, is_valid, created_at 
        FROM conversations 
        WHERE user_id = ? 
        ORDER BY created_at DESC 
        LIMIT ? OFFSET ?
        """,
        (user_id, limit, offset)
    )
    rows = cursor.fetchall()
    conn.close()
    return [dict(row) for row in rows]


def get_conversation_count(user_id: int) -> int:
    """获取用户的对话总数"""
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM conversations WHERE user_id = ?", (user_id,))
    count = cursor.fetchone()[0]
    conn.close()
    return count


def delete_conversation(conv_id: int, user_id: int) -> bool:
    """删除对话记录(验证用户所有权)"""
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute(
        "DELETE FROM conversations WHERE id = ? AND user_id = ?",
        (conv_id, user_id)
    )
    conn.commit()
    deleted = cursor.rowcount > 0
    conn.close()
    return deleted


def clear_user_conversations(user_id: int) -> int:
    """清空用户的所有对话历史,返回删除的记录数"""
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("DELETE FROM conversations WHERE user_id = ?", (user_id,))
    conn.commit()
    deleted = cursor.rowcount
    conn.close()
    return deleted


# ===================== #
# 成就系统相关操作
# ===================== #

def get_user_stats(user_id: int) -> Optional[Dict]:
    """获取用户统计数据"""
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM user_stats WHERE user_id = ?", (user_id,))
    row = cursor.fetchone()
    conn.close()
    
    if row:
        return dict(row)
    else:
        # 创建新记录
        conn = get_db()
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO user_stats (user_id) VALUES (?)",
            (user_id,)
        )
        conn.commit()
        conn.close()
        return {
            "user_id": user_id,
            "total_generations": 0,
            "perfect_scores": 0,
            "good_scores": 0,
            "total_score_sum": 0,
            "consecutive_days": 0,
            "last_active_date": None
        }


def update_user_stats(user_id: int, score: int):
    """更新用户统计数据"""
    from datetime import date
    
    stats = get_user_stats(user_id)
    conn = get_db()
    cursor = conn.cursor()
    
    # 更新统计
    new_total = stats["total_generations"] + 1
    new_perfect = stats["perfect_scores"] + (1 if score >= 90 else 0)
    new_good = stats["good_scores"] + (1 if score >= 80 else 0)
    new_sum = stats["total_score_sum"] + score
    
    # 检查连续天数
    today = date.today()
    last_date = stats["last_active_date"]
    consecutive = stats["consecutive_days"]
    
    if last_date:
        last_date_obj = date.fromisoformat(last_date) if isinstance(last_date, str) else last_date
        if (today - last_date_obj).days == 1:
            consecutive += 1
        elif (today - last_date_obj).days > 1:
            consecutive = 1
    else:
        consecutive = 1
    
    cursor.execute(
        """
        UPDATE user_stats 
        SET total_generations = ?, 
            perfect_scores = ?, 
            good_scores = ?,
            total_score_sum = ?,
            consecutive_days = ?,
            last_active_date = ?
        WHERE user_id = ?
        """,
        (new_total, new_perfect, new_good, new_sum, consecutive, today.isoformat(), user_id)
    )
    conn.commit()
    conn.close()
    
    # 检查并解锁成就
    check_and_unlock_achievements(user_id, new_total, new_perfect, consecutive, score)


def check_and_unlock_achievements(user_id: int, total: int, perfect: int, consecutive: int, latest_score: int):
    """检查并解锁成就"""
    new_achievements = []
    
    # 定义成就规则
    achievements = [
        ("first_code", "🎓 初来乍到", total >= 1),
        ("code_master_10", "🚀 代码新手", total >= 10),
        ("code_master_50", "💻 代码达人", total >= 50),
        ("code_master_100", "🏆 代码大师", total >= 100),
        ("perfectionist", "💯 完美主义者", perfect >= 5),
        ("super_perfectionist", "⭐ 超级完美", perfect >= 20),
        ("consecutive_3", "🔥 坚持不懈", consecutive >= 3),
        ("consecutive_7", "📅 一周连续", consecutive >= 7),
        ("first_perfect", "✨ 首个满分", latest_score >= 90),
    ]
    
    # 获取已解锁的成就
    unlocked = get_user_achievements(user_id)
    unlocked_types = [a["achievement_type"] for a in unlocked]
    
    # 检查新成就
    for ach_type, ach_name, condition in achievements:
        if condition and ach_type not in unlocked_types:
            unlock_achievement(user_id, ach_type, ach_name)
            new_achievements.append({"type": ach_type, "name": ach_name})
    
    return new_achievements


def unlock_achievement(user_id: int, achievement_type: str, achievement_name: str):
    """解锁成就"""
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO achievements (user_id, achievement_type, achievement_name) VALUES (?, ?, ?)",
        (user_id, achievement_type, achievement_name)
    )
    conn.commit()
    conn.close()


def get_user_achievements(user_id: int) -> List[Dict]:
    """获取用户已解锁的成就"""
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute(
        "SELECT * FROM achievements WHERE user_id = ? ORDER BY unlocked_at DESC",
        (user_id,)
    )
    rows = cursor.fetchall()
    conn.close()
    return [dict(row) for row in rows]


# 初始化数据库
if __name__ == "__main__":
    init_db()
